using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
//using IBM.Data.DB2;

namespace ldapAccountGen
{
    class DBAdapter
    {
        // Get Project Settings
        private Properties.Settings settings = Properties.Settings.Default;
        //private DB2Connection db2conn = new DB2Connection();
        private SqlConnection msconn = new SqlConnection();
        private string uDbType;

        public DBAdapter(string connType)
        {
            dbType = connType;
            if (connType == "DB2")
            {                
                //db2conn = ConnectDB(settings.PANSY1conn);
            }
            else if (connType == "MS")
            {
                msconn = msConnectDB(settings.dataUC);
            }
            else if (connType == "MySQL")
            {
            }
            else
            {
            }
        }
//        public DB2Connection ConnectDB(string connStr)
//        {
//            db2conn.ConnectionString = connStr;
//            try
//            {
//                db2conn.Open();
//            }
//            catch (Exception exp)
//            {
//                Console.WriteLine("Cannot connect: " + exp.Message);
//            }
//            return db2conn;
//        }
        public SqlConnection msConnectDB(string connStr)
        {
            msconn.ConnectionString = settings.dataUC;
            try
            {
                msconn.Open();
            }
            catch (Exception exp)
            {
                Console.WriteLine("Cannot connect: " + exp.Message);
            }
            return msconn;
        }
//        public DB2Command CommandDB(string coll)
//        {
//            DB2Command cmd = db2conn.CreateCommand();
//            cmd.CommandText = @"SELECT DISTINCT
//                         SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.USERNAME
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.LASTNAME
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.FIRSTNAME
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.BIRTHDATE
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTTERM.PRIMPGMCOLLEGE
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.EMAIL
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTTERM.PRIMPGMAREACODE
//                    ,    SEAM_VIEWS_LSA.SEAM_STUDENTTERM.PRIMACADPGM
//                     FROM         
//                         SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY, SEAM_VIEWS_LSA.SEAM_STUDENTTERM
//                     WHERE     
//                         SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.STUDENTID = SEAM_VIEWS_LSA.SEAM_STUDENTTERM.STUDENTID
//                     AND
//                         SEAM_VIEWS_LSA.SEAM_STUDENTTERM.PRIMPGMCOLLEGE = '" + coll + "'" +
//                  "  ORDER BY SEAM_VIEWS_LSA.SEAM_STUDENTPRIMARY.LASTNAME";
//            return cmd;
//        }
        public SqlCommand msCommandDB()
        {
            SqlCommand cmd = msconn.CreateCommand();
            cmd.CommandText = @"SELECT DISTINCT
                            USERNAME
                        ,   FIRSTNAME
                        ,   LASTNAME
                        ,   BIRTHDATE
                        ,   PRIMPGMCOLLEGE
                        ,   EMAIL
                        ,   PRIMPGMAREACODE
                        ,   PRIMACADPGM
                    FROM Students
                    ORDER BY USERNAME,PRIMPGMCOLLEGE";
            return cmd;
        }
//        public DataSet FillDS(DB2Command cmd)
//        {
//            DataSet dset = new DataSet();
//            cmd.ExecuteNonQuery();
//            DB2DataAdapter adp = new DB2DataAdapter();
//            adp.SelectCommand = cmd;
//            adp.Fill(dset);
//            return dset;
//        }
//        public void Close()
//        {
//            db2conn.Close();
//            db2conn.Dispose();
//        }
        public void msClose()
        {
            msconn.Close();
            msconn.Dispose();
        }
        public string dbType
        {
            set { uDbType = value; }
            get { return uDbType; }
        }
    }
}
